﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
//using Microsoft.Practices.EnterpriseLibrary.Common;
using Domain.Entity;

namespace Domain.DAL
{
    public abstract class DALBase
    {

        protected Database db = null;

        protected DbTransaction Transaction { get; set; }

        private DbConnection _connection { get; set; }

        /// <summary>
        /// 启动一个事务
        /// </summary>
        protected void StartTran()
        {
            _connection = db.CreateConnection();
            _connection.Open();
            Transaction = _connection.BeginTransaction();
        }

        /// <summary>
        /// 取消事务，回滚
        /// </summary>
        protected void CancelTran()
        {
            Transaction.Rollback();
            EndTran();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        protected void CommitTran()
        {
            Transaction.Commit();
            EndTran();
        }

        /// <summary>
        /// 结束事物
        /// </summary>
        private void EndTran()
        {
            if (Transaction != null)
            {
                Transaction.Dispose();
            }

            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
            }
        }

        /// <summary>
        /// 默认数据库链接字符串的name值，如<dataConfiguration defaultDatabase="mySQLDB"/>中defaultDatabase的值("mySQLDB")即为默认数据库链接
        /// </summary>
        protected DALBase()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        /// <summary>
        /// 自定义数据库链接字符串的name值
        /// </summary>
        /// <param name="connectString">connectionStrings配置节的name值</param>
        protected DALBase(string connectString)
        {
            db = DatabaseFactory.CreateDatabase(connectString);
        }

        /// <summary>
        /// 单条查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <returns></returns>
        protected T ExecuteReader<T>(DbCommand cmd) where T : IEntityBase, new()
        {
            T model = new T();
            using (IDataReader idr = db.ExecuteReader(cmd))
            {
                if (idr.Read())
                {
                    IList<string> fields = GetFieldsFromIDataReader(idr);
                    model.Fill(idr, fields);
                    return model;
                }
                return default(T);
            }
        }

        /// <summary>
        /// 列表查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cmd"></param>
        /// <returns></returns>
        protected List<T> ExecuteReaderList<T>(DbCommand cmd) where T : IEntityBase, new()
        {
            List<T> ModelList = new List<T>();
            using (IDataReader idr = db.ExecuteReader(cmd))
            {
                IList<string> fields = GetFieldsFromIDataReader(idr);
                while (idr.Read())
                {
                    T item = new T();
                    item.Fill(idr, fields);
                    ModelList.Add(item);
                }
                return ModelList;
            }
        }

        /// <summary>
        /// 存储过程分页查询【UtilPage】
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="output"></param>
        /// <param name="orderBy"></param>
        /// <param name="where"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        protected List<T> GetPageDetail<T>(string tableName, string output, string orderBy, string where, int pageSize, int pageIndex, out int total) where T : IEntityBase, new()
        {
            List<T> result = new List<T>();
            using (DbCommand cmd = db.GetStoredProcCommand("UtilPage"))
            {
                db.AddInParameter(cmd, "@datasrc", DbType.String, tableName);
                db.AddInParameter(cmd, "@fieldlist", DbType.String, output);
                db.AddInParameter(cmd, "@orderby", DbType.String, orderBy);
                db.AddInParameter(cmd, "@filter", DbType.String, where);
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageIndex);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                using (IDataReader idr = db.ExecuteReader(cmd))
                {
                    total = 0;
                    if (idr.Read())
                    {
                        total = (int)idr[0];
                    }
                    if (idr.NextResult())
                    {
                        IList<string> fields = GetFieldsFromIDataReader(idr);
                        while (idr.Read())
                        {
                            T item = new T();
                            item.Fill(idr, fields);
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }
        
        /// <summary>
        /// 存储过程分页查询，支持多表联合查询【UtilPageByJoin】
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableSource"></param>
        /// <param name="output"></param>
        /// <param name="orderBy"></param>
        /// <param name="where"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        protected List<T> GetPageDetailByJoin<T>(string tableSource, string output, string orderBy, string where, int pageSize, int pageIndex, out int total) where T : IEntityBase, new()
        {
            List<T> result = new List<T>();
            using (DbCommand cmd = db.GetStoredProcCommand("UtilPageByJoin"))
            {
                db.AddInParameter(cmd, "@datasrc", DbType.String, tableSource);
                db.AddInParameter(cmd, "@fieldlist", DbType.String, output);
                db.AddInParameter(cmd, "@orderby", DbType.String, orderBy);
                db.AddInParameter(cmd, "@filter", DbType.String, where);
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageIndex);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                using (IDataReader idr = db.ExecuteReader(cmd))
                {
                    total = 0;
                    if (idr.Read())
                    {
                        total = (int)idr[0];
                    }
                    if (idr.NextResult())
                    {
                        IList<string> fields = GetFieldsFromIDataReader(idr);
                        while (idr.Read())
                        {
                            T item = new T();
                            item.Fill(idr, fields);
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 分页 where条件在tablename一起【UtilPageNoWhere】
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="output"></param>
        /// <param name="orderBy"></param>
        /// <param name="where"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<T> GetPageDetailNoWhere<T>(string tableName, string output, string orderBy, int pageSize, int pageIndex, out int total) where T : IEntityBase, new()
        {
            List<T> result = new List<T>();
            using (DbCommand cmd = db.GetStoredProcCommand("UtilPageNoWhere"))
            {
                db.AddInParameter(cmd, "@datasrc", DbType.String, tableName);
                db.AddInParameter(cmd, "@fieldlist", DbType.String, output);
                db.AddInParameter(cmd, "@orderby", DbType.String, orderBy);
                // db.AddInParameter(cmd, "@filter", DbType.String, where);
                db.AddInParameter(cmd, "@PageNum", DbType.Int32, pageIndex);
                db.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);

                using (IDataReader idr = db.ExecuteReader(cmd))
                {
                    total = 0;
                    if (idr.Read())
                    {
                        total = (int)idr[0];
                    }
                    if (idr.NextResult())
                    {
                        IList<string> fields = GetFieldsFromIDataReader(idr);
                        while (idr.Read())
                        {
                            T item = new T();
                            item.Fill(idr, fields);
                            result.Add(item);
                        }
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 从IDataReader,获取数据列集合
        /// </summary>
        /// <param name="idr">IDataReader</param>
        /// <returns></returns>
        public static IList<string> GetFieldsFromIDataReader(IDataReader idr)
        {
            IList<string> fields = new List<string>();
            int count = idr.FieldCount;
            for (int i = 0; i < count; i++)
            {
                fields.Add(idr.GetName(i));
            }
            return fields;
        }
    }
}
